package com.qlm.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qlm.model.stuInfo;

public class stuInfoService {

	private Connection conn;
	private PreparedStatement pstmt;
	
	public stuInfoService(){
		conn = new com.qlm.conn.conn().getCon();
	}
	
	public boolean addStu(stuInfo stu){
		try {
			pstmt = conn.prepareStatement("insert into studentinfo (nicheng,truename,sex,birthday,major,course,interest,remark) values(?,?,?,?,?,?,?,?)");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			pstmt.setString(1,stu.getNicheng());
			pstmt.setString(2, stu.getTruename());
			pstmt.setByte(3, stu.getSex());
			pstmt.setString(4, stu.getBirthday());
			pstmt.setString(5, stu.getMajor());
			pstmt.setString(6, stu.getCourse());
			pstmt.setString(7, stu.getInterest());
			pstmt.setString(8, stu.getRemark());
			
			pstmt.executeUpdate();
			
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}finally{
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	public List<stuInfo> queryAllStu() throws SQLException {
		List<stuInfo> stus = new ArrayList<stuInfo>();
		try {
			pstmt = conn.prepareStatement("select * from studentinfo");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		ResultSet rs = pstmt.executeQuery();
		try{
			while(rs.next()){
				stuInfo stu = new stuInfo();
				stu.setId(rs.getInt(1));
				stu.setNicheng(rs.getString(2));
				stu.setTruename(rs.getString(3));
				stu.setSex(rs.getByte(4));
				if(rs.getDate(5) != null)
					stu.setBirthday(rs.getDate(5).toString());
				stu.setMajor(rs.getString(6));
				if(rs.getString(7) != null)
					stu.setCourse(rs.getString(7));
				if(rs.getString(8) != null)
					stu.setInterest(rs.getString(8));
				stu.setRemark(rs.getString(9));
				stus.add(stu);
			}
		}	
		finally{
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return stus;
		}
	}
	
	public stuInfo queryStubyID(int id) throws SQLException {
		try {
			pstmt = conn.prepareStatement("select * from studentinfo where id=?");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		try {
			if(rs.next()){
				stuInfo stu = new stuInfo();
				stu.setId(rs.getInt(1));
				stu.setNicheng(rs.getString(2));
				stu.setTruename(rs.getString(3));
				stu.setSex(rs.getByte(4));
				if(rs.getDate(5) != null)
					stu.setBirthday(rs.getDate(5).toString());
				stu.setMajor(rs.getString(6));
				if(rs.getString(7) != null)
					stu.setCourse(rs.getString(7));
				if(rs.getString(8) != null)
					stu.setInterest(rs.getString(8));
				stu.setRemark(rs.getString(9));
				return stu;
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally{
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	public boolean updateStu(stuInfo stu) {
		
		try {
			pstmt = conn.prepareStatement("update studentinfo set nicheng=?,truename=?,sex=?,birthday=?,major=?,course=?,remark=? where id=?");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {                                       
			pstmt.setString(1,stu.getNicheng());
			pstmt.setString(2, stu.getTruename());
			pstmt.setByte(3, stu.getSex());
			pstmt.setString(4, stu.getBirthday());
			pstmt.setString(5, stu.getMajor());
			pstmt.setString(6, stu.getCourse());
			pstmt.setString(7, stu.getInterest());
			pstmt.setString(7, stu.getRemark());
			pstmt.setInt(8, stu.getId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public Boolean deleteStu(int id) {
		try {
			pstmt = conn.prepareStatement("delete from studentinfo where id=?");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally{
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
}
